-- **************************************************************************
-- ** 创建者   : suning
-- ** 创建日期 : 20230509
-- ** 功能描述 ：交件准点率汇总
-- **************************************************************************
-- **************************** 修改日志 ************************************
-- **************************************************************************
-- ** 依赖表：交件准点率明细
-- **************************************************************************
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
-- **************************************************************************
drop table if exists jms_dm.dm_waybill_collect_intime_plan_summary_dt;
create external table if not exists jms_dm.dm_waybill_collect_intime_plan_summary_dt(
    network_code                string            comment'交件网点编码'
   ,network_name                string            comment'交件网点名称'
   ,network_fran_code           string            comment'交件网点加盟商编码'
   ,network_fran_name           string            comment'交件网点加盟商名称'
   ,network_agent_code          string            comment'交件网点代理区编码'
   ,network_agent_name          string            comment'交件网点代理区名称'
   ,nodal_code                  string            comment'交件集散编码'
   ,nodal_name                  string            comment'交件集散名称'
   ,nodal_fran_code             string            comment'实际交件集散点加盟商编码'
   ,nodal_fran_name             string            comment'实际交件集散点加盟商名称'
   ,nodal_agent_code            string            comment'实际交件集散点代理区编码'
   ,nodal_agent_name            string            comment'实际交件集散点代理区名称'
   ,jj_cnt                      bigint            comment'应交件票数'
   ,jj_zd_cnt                   bigint            comment'准点交件票数'
   ,jj_bzd_cnt                  bigint            comment'不准点交件票数'
   ,jj_network_cnt              bigint            comment'交件网点责任票数'
   ,jj_nodal_cnt                bigint            comment'交件集散责任票数'
   ,effect_qs_cnt               bigint            comment'时效缺失票数'
   ,three_code_cnt              bigint            comment'三段码问题票数'
   ,error_send_cnt              bigint            comment'错发票数'
   ,other_cnt                   bigint            comment'其他票数'
   ,local_city_cnt              bigint            comment'同城件'
   ,weidao_jj_cnt               bigint            comment'未到交件时间票数'
   ,route_late_cnt              bigint            comment'非最优路由票数'
   ,center_send_timely_cnt      bigint            comment'交件晚点及时发出票数'
   ,center_send_late_cnt        bigint            comment'交件晚点未及时发出票数'
   ,effect_bzd_cnt              bigint            comment'时效不准点票数'
   ,qxt_jj_cnt                  bigint            comment'七星潭应交件票数'
   ,qxt_jj_zd_cnt               bigint            comment'七星潭准点交件票数'
   ,qxt_jj_bzd_cnt              bigint            comment'七星潭不准点交件票数'
   ,qxt_jj_network_cnt          bigint            comment'七星潭交件网点责任票数'
   ,qxt_jj_nodal_cnt            bigint            comment'七星潭交件集散责任票数'
   ,qxt_effect_qs_cnt           bigint            comment'七星潭时效缺失票数'
   ,qxt_three_code_cnt          bigint            comment'七星潭三段码问题票数'
   ,qxt_error_send_cnt          bigint            comment'七星潭错发票数'
   ,qxt_other_cnt               bigint            comment'七星潭其他票数'
   ,qxt_local_city_cnt          bigint            comment'七星潭同城件'
   ,qxt_weidao_jj_cnt           bigint            comment'七星潭未到交件时间票数'
   ,qxt_route_late_cnt          bigint            comment'七星潭非最优路由票数'
   ,qxt_center_send_timely_cnt  bigint            comment'七星潭交件晚点及时发出票数'
   ,qxt_center_send_late_cnt    bigint            comment'七星潭交件晚点未及时发出票数'
   ,qxt_effect_bzd_cnt          bigint            comment'七星潭时效不准点票数'
   ,thd_jj_cnt                  bigint            comment'桃花岛应交件票数'
   ,thd_jj_zd_cnt               bigint            comment'桃花岛准点交件票数'
   ,thd_jj_bzd_cnt              bigint            comment'桃花岛不准点交件票数'
   ,thd_jj_network_cnt          bigint            comment'桃花岛交件网点责任票数'
   ,thd_jj_nodal_cnt            bigint            comment'桃花岛交件集散责任票数'
   ,thd_effect_qs_cnt           bigint            comment'桃花岛时效缺失票数'
   ,thd_three_code_cnt          bigint            comment'桃花岛三段码问题票数'
   ,thd_error_send_cnt          bigint            comment'桃花岛错发票数'
   ,thd_other_cnt               bigint            comment'桃花岛其他票数'
   ,thd_local_city_cnt          bigint            comment'桃花岛同城件'
   ,thd_weidao_jj_cnt           bigint            comment'桃花岛未到交件时间票数'
   ,thd_route_late_cnt          bigint            comment'桃花岛非最优路由票数'
   ,thd_center_send_timely_cnt  bigint            comment'桃花岛交件晚点及时发出票数'
   ,thd_center_send_late_cnt    bigint            comment'桃花岛交件晚点未及时发出票数'
   ,thd_effect_bzd_cnt          bigint            comment'桃花岛时效不准点票数'
   ,zjs_jj_cnt                  bigint            comment'紫金山应交件票数'
   ,zjs_jj_zd_cnt               bigint            comment'紫金山准点交件票数'
   ,zjs_jj_bzd_cnt              bigint            comment'紫金山不准点交件票数'
   ,zjs_jj_network_cnt          bigint            comment'紫金山交件网点责任票数'
   ,zjs_jj_nodal_cnt            bigint            comment'紫金山交件集散责任票数'
   ,zjs_effect_qs_cnt           bigint            comment'紫金山时效缺失票数'
   ,zjs_three_code_cnt          bigint            comment'紫金山三段码问题票数'
   ,zjs_error_send_cnt          bigint            comment'紫金山错发票数'
   ,zjs_other_cnt               bigint            comment'紫金山其他票数'
   ,zjs_local_city_cnt          bigint            comment'紫金山同城件'
   ,zjs_weidao_jj_cnt           bigint            comment'紫金山未到交件时间票数'
   ,zjs_route_late_cnt          bigint            comment'紫金山非最优路由票数'
   ,zjs_center_send_timely_cnt  bigint            comment'紫金山交件晚点及时发出票数'
   ,zjs_center_send_late_cnt    bigint            comment'紫金山交件晚点未及时发出票数'
   ,zjs_effect_bzd_cnt          bigint            comment'紫金山时效不准点票数'
   ,xyf_jj_cnt                  bigint            comment'逍遥峰应交件票数'
   ,xyf_jj_zd_cnt               bigint            comment'逍遥峰准点交件票数'
   ,xyf_jj_bzd_cnt              bigint            comment'逍遥峰不准点交件票数'
   ,xyf_jj_network_cnt          bigint            comment'逍遥峰交件网点责任票数'
   ,xyf_jj_nodal_cnt            bigint            comment'逍遥峰交件集散责任票数'
   ,xyf_effect_qs_cnt           bigint            comment'逍遥峰时效缺失票数'
   ,xyf_three_code_cnt          bigint            comment'逍遥峰三段码问题票数'
   ,xyf_error_send_cnt          bigint            comment'逍遥峰错发票数'
   ,xyf_other_cnt               bigint            comment'逍遥峰其他票数'
   ,xyf_local_city_cnt          bigint            comment'逍遥峰同城件'
   ,xyf_weidao_jj_cnt           bigint            comment'逍遥峰未到交件时间票数'
   ,xyf_route_late_cnt          bigint            comment'逍遥峰非最优路由票数'
   ,xyf_center_send_timely_cnt  bigint            comment'逍遥峰交件晚点及时发出票数'
   ,xyf_center_send_late_cnt    bigint            comment'逍遥峰交件晚点未及时发出票数'
   ,xyf_effect_bzd_cnt          bigint            comment'逍遥峰时效不准点票数'
   ,jdw_jj_cnt                  bigint            comment'极地湾应交件票数'
   ,jdw_jj_zd_cnt               bigint            comment'极地湾准点交件票数'
   ,jdw_jj_bzd_cnt              bigint            comment'极地湾不准点交件票数'
   ,jdw_jj_network_cnt          bigint            comment'极地湾交件网点责任票数'
   ,jdw_jj_nodal_cnt            bigint            comment'极地湾交件集散责任票数'
   ,jdw_effect_qs_cnt           bigint            comment'极地湾时效缺失票数'
   ,jdw_three_code_cnt          bigint            comment'极地湾三段码问题票数'
   ,jdw_error_send_cnt          bigint            comment'极地湾错发票数'
   ,jdw_other_cnt               bigint            comment'极地湾其他票数'
   ,jdw_local_city_cnt          bigint            comment'极地湾同城件'
   ,jdw_weidao_jj_cnt           bigint            comment'极地湾未到交件时间票数'
   ,jdw_route_late_cnt          bigint            comment'极地湾非最优路由票数'
   ,jdw_center_send_timely_cnt  bigint            comment'极地湾交件晚点及时发出票数'
   ,jdw_center_send_late_cnt    bigint            comment'极地湾交件晚点未及时发出票数'
   ,jdw_effect_bzd_cnt          bigint            comment'极地湾时效不准点票数'
   ,qt_jj_cnt                   bigint            comment'其他应交件票数'
   ,qt_jj_zd_cnt                bigint            comment'其他准点交件票数'
   ,qt_jj_bzd_cnt               bigint            comment'其他不准点交件票数'
   ,qt_jj_network_cnt           bigint            comment'其他交件网点责任票数'
   ,qt_jj_nodal_cnt             bigint            comment'其他交件集散责任票数'
   ,qt_effect_qs_cnt            bigint            comment'其他时效缺失票数'
   ,qt_three_code_cnt           bigint            comment'其他三段码问题票数'
   ,qt_error_send_cnt           bigint            comment'其他错发票数'
   ,qt_other_cnt                bigint            comment'其他其他票数'
   ,qt_local_city_cnt           bigint            comment'其他同城件'
   ,qt_weidao_jj_cnt            bigint            comment'其他未到交件时间票数'
   ,qt_route_late_cnt           bigint            comment'其他非最优路由票数'
   ,qt_center_send_timely_cnt   bigint            comment'其他交件晚点及时发出票数'
   ,qt_center_send_late_cnt     bigint            comment'其他交件晚点未及时发出票数'
   ,qt_effect_bzd_cnt           bigint            comment'其他时效不准点票数'
   ,exempt_type                 string            comment'豁免类型'
   ,fine_amt                    decimal(15,2)     comment'罚款金额'
) comment '交件准点率汇总'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_waybill_collect_intime_plan_summary_dt'
tblproperties (
  'discover.partitions' = 'false',
  'parquet.column.index.access' = 'true'
);
